{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 加载数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore') # 忽略警告"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "code_folding": [
     0
    ],
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'13</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>2785</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'16</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>3133</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'09</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>3901</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4'21</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>4946</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3'44</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13</td>\n",
       "      <td>9</td>\n",
       "      <td>3538</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别 男1000米跑  男50米跑    男跳远  男体前屈  男引体  男肺活量     身高    体重  BMI\n",
       "0   1  男    4'13   8.88  195.0    12    1  2785  170.0  72.6    0\n",
       "1   1  男    4'16   7.70  225.0    11    7  3133  174.0  52.7    0\n",
       "2   1  男    4'09   8.45  218.0    14    1  3901  169.0  46.5    0\n",
       "3   1  男    4'21   8.05  206.0    13    1  4946  183.0  79.7    0\n",
       "4   1  男    3'44   7.52  210.0    13    9  3538  171.0  54.7    0"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#男生\n",
    "df_boy = pd.read_excel('./18级高一体测成绩汇总.xls')\n",
    "df_boy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>9.32</td>\n",
       "      <td>185.0</td>\n",
       "      <td>16</td>\n",
       "      <td>48</td>\n",
       "      <td>3775</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>11.44</td>\n",
       "      <td>148.0</td>\n",
       "      <td>9</td>\n",
       "      <td>29</td>\n",
       "      <td>3683</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>13.40</td>\n",
       "      <td>150.0</td>\n",
       "      <td>7</td>\n",
       "      <td>40</td>\n",
       "      <td>3331</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>9.52</td>\n",
       "      <td>172.0</td>\n",
       "      <td>21</td>\n",
       "      <td>46</td>\n",
       "      <td>3701</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>9.79</td>\n",
       "      <td>145.0</td>\n",
       "      <td>8</td>\n",
       "      <td>34</td>\n",
       "      <td>3592</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  女800米跑  女50米跑    女跳远  女体前屈  女仰卧  女肺活量     身高    体重  BMI\n",
       "0   1  女    3.22   9.32  185.0    16   48  3775  163.0  51.3    0\n",
       "1   1  女    4.59  11.44  148.0     9   29  3683  163.0  66.6    0\n",
       "2   1  女    3.46  13.40  150.0     7   40  3331  157.0  60.0    0\n",
       "3   1  女    3.39   9.52  172.0    21   46  3701  160.0  50.7    0\n",
       "4   1  女    3.43   9.79  145.0     8   34  3592  167.0  63.9    0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 女生\n",
    "df_girl = pd.read_excel('./18级高一体测成绩汇总.xls',sheet_name=1)\n",
    "df_girl.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">男肺活量</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女肺活量</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男50米跑</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女50米跑</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男体前屈</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女跳远</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男引体</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女仰卧</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男1000米跑</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女800米跑</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>...</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4540</td>\n",
       "      <td>100</td>\n",
       "      <td>3150</td>\n",
       "      <td>100</td>\n",
       "      <td>7.1</td>\n",
       "      <td>100</td>\n",
       "      <td>7.8</td>\n",
       "      <td>100</td>\n",
       "      <td>23.6</td>\n",
       "      <td>100</td>\n",
       "      <td>...</td>\n",
       "      <td>204</td>\n",
       "      <td>100</td>\n",
       "      <td>16.0</td>\n",
       "      <td>100</td>\n",
       "      <td>53</td>\n",
       "      <td>100</td>\n",
       "      <td>3'30\"</td>\n",
       "      <td>100</td>\n",
       "      <td>3'24\"</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4420</td>\n",
       "      <td>95</td>\n",
       "      <td>3100</td>\n",
       "      <td>95</td>\n",
       "      <td>7.2</td>\n",
       "      <td>95</td>\n",
       "      <td>7.9</td>\n",
       "      <td>95</td>\n",
       "      <td>21.5</td>\n",
       "      <td>95</td>\n",
       "      <td>...</td>\n",
       "      <td>198</td>\n",
       "      <td>95</td>\n",
       "      <td>15.0</td>\n",
       "      <td>95</td>\n",
       "      <td>51</td>\n",
       "      <td>95</td>\n",
       "      <td>3'35\"</td>\n",
       "      <td>95</td>\n",
       "      <td>3'30\"</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4300</td>\n",
       "      <td>90</td>\n",
       "      <td>3050</td>\n",
       "      <td>90</td>\n",
       "      <td>7.3</td>\n",
       "      <td>90</td>\n",
       "      <td>8.0</td>\n",
       "      <td>90</td>\n",
       "      <td>19.4</td>\n",
       "      <td>90</td>\n",
       "      <td>...</td>\n",
       "      <td>192</td>\n",
       "      <td>90</td>\n",
       "      <td>14.0</td>\n",
       "      <td>90</td>\n",
       "      <td>49</td>\n",
       "      <td>90</td>\n",
       "      <td>3'40\"</td>\n",
       "      <td>90</td>\n",
       "      <td>3'36\"</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4050</td>\n",
       "      <td>85</td>\n",
       "      <td>2900</td>\n",
       "      <td>85</td>\n",
       "      <td>7.4</td>\n",
       "      <td>85</td>\n",
       "      <td>8.3</td>\n",
       "      <td>85</td>\n",
       "      <td>17.2</td>\n",
       "      <td>85</td>\n",
       "      <td>...</td>\n",
       "      <td>185</td>\n",
       "      <td>85</td>\n",
       "      <td>13.0</td>\n",
       "      <td>85</td>\n",
       "      <td>46</td>\n",
       "      <td>85</td>\n",
       "      <td>3'47\"</td>\n",
       "      <td>85</td>\n",
       "      <td>3'43\"</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3800</td>\n",
       "      <td>80</td>\n",
       "      <td>2750</td>\n",
       "      <td>80</td>\n",
       "      <td>7.5</td>\n",
       "      <td>80</td>\n",
       "      <td>8.6</td>\n",
       "      <td>80</td>\n",
       "      <td>15.0</td>\n",
       "      <td>80</td>\n",
       "      <td>...</td>\n",
       "      <td>178</td>\n",
       "      <td>80</td>\n",
       "      <td>12.0</td>\n",
       "      <td>80</td>\n",
       "      <td>43</td>\n",
       "      <td>80</td>\n",
       "      <td>3'55\"</td>\n",
       "      <td>80</td>\n",
       "      <td>3'50\"</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   男肺活量       女肺活量      男50米跑      女50米跑       男体前屈       ...  女跳远        男引体  \\\n",
       "     成绩   分数    成绩   分数    成绩   分数    成绩   分数    成绩   分数  ...   成绩   分数    成绩   \n",
       "0  4540  100  3150  100   7.1  100   7.8  100  23.6  100  ...  204  100  16.0   \n",
       "1  4420   95  3100   95   7.2   95   7.9   95  21.5   95  ...  198   95  15.0   \n",
       "2  4300   90  3050   90   7.3   90   8.0   90  19.4   90  ...  192   90  14.0   \n",
       "3  4050   85  2900   85   7.4   85   8.3   85  17.2   85  ...  185   85  13.0   \n",
       "4  3800   80  2750   80   7.5   80   8.6   80  15.0   80  ...  178   80  12.0   \n",
       "\n",
       "       女仰卧      男1000米跑      女800米跑       \n",
       "    分数  成绩   分数      成绩   分数     成绩   分数  \n",
       "0  100  53  100   3'30\"  100  3'24\"  100  \n",
       "1   95  51   95   3'35\"   95  3'30\"   95  \n",
       "2   90  49   90   3'40\"   90  3'36\"   90  \n",
       "3   85  46   85   3'47\"   85  3'43\"   85  \n",
       "4   80  43   80   3'55\"   80  3'50\"   80  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 评分标准\n",
    "score = pd.read_excel('./体侧成绩评分表.xls',header = [0,1])\n",
    "score.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 数据类型转换"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 将男1000米跑数据，转换成了float数值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>2785</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11</td>\n",
       "      <td>7</td>\n",
       "      <td>3133</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>3901</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>4946</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13</td>\n",
       "      <td>9</td>\n",
       "      <td>3538</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  男1000米跑  男50米跑    男跳远  男体前屈  男引体  男肺活量     身高    体重  BMI\n",
       "0   1  男     4.13   8.88  195.0    12    1  2785  170.0  72.6    0\n",
       "1   1  男     4.16   7.70  225.0    11    7  3133  174.0  52.7    0\n",
       "2   1  男     4.09   8.45  218.0    14    1  3901  169.0  46.5    0\n",
       "3   1  男     4.21   8.05  206.0    13    1  4946  183.0  79.7    0\n",
       "4   1  男     3.44   7.52  210.0    13    9  3538  171.0  54.7    0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def convert(x):\n",
    "    if isinstance(x,str):\n",
    "        m,s = x.split(\"'\")\n",
    "        m,s = int(m),int(s)\n",
    "        return m + s/100.0\n",
    "    else:\n",
    "        return x\n",
    "df_boy['男1000米跑'] = df_boy['男1000米跑'].map(convert)\n",
    "df_boy.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 评分数据，数据类型转换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert(x):\n",
    "    m,s = [int(i) for i in x.strip('\"').split(\"'\")]\n",
    "    return m + s/100.0\n",
    "# 男生评分数据转换\n",
    "score.iloc[:,-4] = score.iloc[:,-4].transform(convert)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 女生评分数据转换\n",
    "score.iloc[:,-2] = score.iloc[:,-2].apply(convert)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">男肺活量</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女肺活量</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男50米跑</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女50米跑</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男体前屈</th>\n",
       "      <th>...</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女跳远</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男引体</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女仰卧</th>\n",
       "      <th colspan=\"2\" halign=\"left\">男1000米跑</th>\n",
       "      <th colspan=\"2\" halign=\"left\">女800米跑</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>...</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "      <th>成绩</th>\n",
       "      <th>分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4540</td>\n",
       "      <td>100</td>\n",
       "      <td>3150</td>\n",
       "      <td>100</td>\n",
       "      <td>7.1</td>\n",
       "      <td>100</td>\n",
       "      <td>7.8</td>\n",
       "      <td>100</td>\n",
       "      <td>23.6</td>\n",
       "      <td>100</td>\n",
       "      <td>...</td>\n",
       "      <td>204</td>\n",
       "      <td>100</td>\n",
       "      <td>16.0</td>\n",
       "      <td>100</td>\n",
       "      <td>53</td>\n",
       "      <td>100</td>\n",
       "      <td>3.30</td>\n",
       "      <td>100</td>\n",
       "      <td>3.24</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4420</td>\n",
       "      <td>95</td>\n",
       "      <td>3100</td>\n",
       "      <td>95</td>\n",
       "      <td>7.2</td>\n",
       "      <td>95</td>\n",
       "      <td>7.9</td>\n",
       "      <td>95</td>\n",
       "      <td>21.5</td>\n",
       "      <td>95</td>\n",
       "      <td>...</td>\n",
       "      <td>198</td>\n",
       "      <td>95</td>\n",
       "      <td>15.0</td>\n",
       "      <td>95</td>\n",
       "      <td>51</td>\n",
       "      <td>95</td>\n",
       "      <td>3.35</td>\n",
       "      <td>95</td>\n",
       "      <td>3.30</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4300</td>\n",
       "      <td>90</td>\n",
       "      <td>3050</td>\n",
       "      <td>90</td>\n",
       "      <td>7.3</td>\n",
       "      <td>90</td>\n",
       "      <td>8.0</td>\n",
       "      <td>90</td>\n",
       "      <td>19.4</td>\n",
       "      <td>90</td>\n",
       "      <td>...</td>\n",
       "      <td>192</td>\n",
       "      <td>90</td>\n",
       "      <td>14.0</td>\n",
       "      <td>90</td>\n",
       "      <td>49</td>\n",
       "      <td>90</td>\n",
       "      <td>3.40</td>\n",
       "      <td>90</td>\n",
       "      <td>3.36</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4050</td>\n",
       "      <td>85</td>\n",
       "      <td>2900</td>\n",
       "      <td>85</td>\n",
       "      <td>7.4</td>\n",
       "      <td>85</td>\n",
       "      <td>8.3</td>\n",
       "      <td>85</td>\n",
       "      <td>17.2</td>\n",
       "      <td>85</td>\n",
       "      <td>...</td>\n",
       "      <td>185</td>\n",
       "      <td>85</td>\n",
       "      <td>13.0</td>\n",
       "      <td>85</td>\n",
       "      <td>46</td>\n",
       "      <td>85</td>\n",
       "      <td>3.47</td>\n",
       "      <td>85</td>\n",
       "      <td>3.43</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3800</td>\n",
       "      <td>80</td>\n",
       "      <td>2750</td>\n",
       "      <td>80</td>\n",
       "      <td>7.5</td>\n",
       "      <td>80</td>\n",
       "      <td>8.6</td>\n",
       "      <td>80</td>\n",
       "      <td>15.0</td>\n",
       "      <td>80</td>\n",
       "      <td>...</td>\n",
       "      <td>178</td>\n",
       "      <td>80</td>\n",
       "      <td>12.0</td>\n",
       "      <td>80</td>\n",
       "      <td>43</td>\n",
       "      <td>80</td>\n",
       "      <td>3.55</td>\n",
       "      <td>80</td>\n",
       "      <td>3.50</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   男肺活量       女肺活量      男50米跑      女50米跑       男体前屈       ...  女跳远        男引体  \\\n",
       "     成绩   分数    成绩   分数    成绩   分数    成绩   分数    成绩   分数  ...   成绩   分数    成绩   \n",
       "0  4540  100  3150  100   7.1  100   7.8  100  23.6  100  ...  204  100  16.0   \n",
       "1  4420   95  3100   95   7.2   95   7.9   95  21.5   95  ...  198   95  15.0   \n",
       "2  4300   90  3050   90   7.3   90   8.0   90  19.4   90  ...  192   90  14.0   \n",
       "3  4050   85  2900   85   7.4   85   8.3   85  17.2   85  ...  185   85  13.0   \n",
       "4  3800   80  2750   80   7.5   80   8.6   80  15.0   80  ...  178   80  12.0   \n",
       "\n",
       "       女仰卧      男1000米跑      女800米跑       \n",
       "    分数  成绩   分数      成绩   分数     成绩   分数  \n",
       "0  100  53  100    3.30  100   3.24  100  \n",
       "1   95  51   95    3.35   95   3.30   95  \n",
       "2   90  49   90    3.40   90   3.36   90  \n",
       "3   85  46   85    3.47   85   3.43   85  \n",
       "4   80  43   80    3.55   80   3.50   80  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看转换后评分标准\n",
    "score.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 男生女生数据，都进行类型转换，数值型的都变成float32"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>9.32</td>\n",
       "      <td>185.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.299999</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>11.44</td>\n",
       "      <td>148.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.599998</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>13.40</td>\n",
       "      <td>150.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>9.52</td>\n",
       "      <td>172.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.700001</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>9.79</td>\n",
       "      <td>145.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.900002</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  女800米跑  女50米跑    女跳远  女体前屈   女仰卧    女肺活量     身高         体重  BMI\n",
       "0   1  女    3.22   9.32  185.0  16.0  48.0  3775.0  163.0  51.299999  0.0\n",
       "1   1  女    4.59  11.44  148.0   9.0  29.0  3683.0  163.0  66.599998  0.0\n",
       "2   1  女    3.46  13.40  150.0   7.0  40.0  3331.0  157.0  60.000000  0.0\n",
       "3   1  女    3.39   9.52  172.0  21.0  46.0  3701.0  160.0  50.700001  0.0\n",
       "4   1  女    3.43   9.79  145.0   8.0  34.0  3592.0  167.0  63.900002  0.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_boy.loc[:,'男1000米跑':] = df_boy.loc[:,'男1000米跑':].astype('float32',copy = False)\n",
    "df_girl.loc[:,'女800米跑':] = df_girl.loc[:,'女800米跑':].astype('float32',copy = False)\n",
    "df_girl.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 男生分数转换"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对体测成绩进行转换，跑步类（越小越好）；跳远、体前屈（越大越好）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 速度类成绩分数转换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "      <th>男50米跑分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.599998</td>\n",
       "      <td>0.0</td>\n",
       "      <td>72</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.700001</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.500000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>74</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.699997</td>\n",
       "      <td>0.0</td>\n",
       "      <td>68</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.700001</td>\n",
       "      <td>0.0</td>\n",
       "      <td>85</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  男1000米跑  男50米跑    男跳远  男体前屈  男引体    男肺活量     身高         体重  BMI  \\\n",
       "0   1  男     4.13   8.88  195.0  12.0  1.0  2785.0  170.0  72.599998  0.0   \n",
       "1   1  男     4.16   7.70  225.0  11.0  7.0  3133.0  174.0  52.700001  0.0   \n",
       "2   1  男     4.09   8.45  218.0  14.0  1.0  3901.0  169.0  46.500000  0.0   \n",
       "3   1  男     4.21   8.05  206.0  13.0  1.0  4946.0  183.0  79.699997  0.0   \n",
       "4   1  男     3.44   7.52  210.0  13.0  9.0  3538.0  171.0  54.700001  0.0   \n",
       "\n",
       "   男1000米跑分数  男50米跑分数  \n",
       "0         72       66  \n",
       "1         70       78  \n",
       "2         74       70  \n",
       "3         68       74  \n",
       "4         85       78  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = ['男1000米跑','男50米跑']\n",
    "for col in cols:\n",
    "    def convert(x):\n",
    "        if x == 0: # 说明没有参加体能测试\n",
    "            return 0\n",
    "        for i in range(score[col].shape[0]):\n",
    "            if x <= score[col]['成绩'][i]:\n",
    "                return score[col]['分数'][i]\n",
    "        return 0 # 说明跑的太慢了\n",
    "    df_boy[col + '分数'] = df_boy[col].map(convert)\n",
    "df_boy.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 跳远类成绩"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "      <th>男50米跑分数</th>\n",
       "      <th>男跳远分数</th>\n",
       "      <th>男体前屈分数</th>\n",
       "      <th>男引体分数</th>\n",
       "      <th>男肺活量分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.599998</td>\n",
       "      <td>0.0</td>\n",
       "      <td>72</td>\n",
       "      <td>66</td>\n",
       "      <td>60</td>\n",
       "      <td>74</td>\n",
       "      <td>0</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.700001</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70</td>\n",
       "      <td>78</td>\n",
       "      <td>74</td>\n",
       "      <td>74</td>\n",
       "      <td>60</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.500000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>74</td>\n",
       "      <td>70</td>\n",
       "      <td>70</td>\n",
       "      <td>78</td>\n",
       "      <td>0</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.699997</td>\n",
       "      <td>0.0</td>\n",
       "      <td>68</td>\n",
       "      <td>74</td>\n",
       "      <td>64</td>\n",
       "      <td>76</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.700001</td>\n",
       "      <td>0.0</td>\n",
       "      <td>85</td>\n",
       "      <td>78</td>\n",
       "      <td>66</td>\n",
       "      <td>76</td>\n",
       "      <td>68</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  男1000米跑  男50米跑    男跳远  男体前屈  男引体    男肺活量     身高         体重  BMI  \\\n",
       "0   1  男     4.13   8.88  195.0  12.0  1.0  2785.0  170.0  72.599998  0.0   \n",
       "1   1  男     4.16   7.70  225.0  11.0  7.0  3133.0  174.0  52.700001  0.0   \n",
       "2   1  男     4.09   8.45  218.0  14.0  1.0  3901.0  169.0  46.500000  0.0   \n",
       "3   1  男     4.21   8.05  206.0  13.0  1.0  4946.0  183.0  79.699997  0.0   \n",
       "4   1  男     3.44   7.52  210.0  13.0  9.0  3538.0  171.0  54.700001  0.0   \n",
       "\n",
       "   男1000米跑分数  男50米跑分数  男跳远分数  男体前屈分数  男引体分数  男肺活量分数  \n",
       "0         72       66     60      74      0      62  \n",
       "1         70       78     74      74     60      68  \n",
       "2         74       70     70      78      0      80  \n",
       "3         68       74     64      76      0     100  \n",
       "4         85       78     66      76     68      74  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = ['男跳远', '男体前屈', '男引体', '男肺活量']\n",
    "\n",
    "for col in cols:\n",
    "    def convert(x):\n",
    "        if x == 0: # 说明没有参加体能测试\n",
    "            return 0\n",
    "        for i in range(score[col].shape[0]):\n",
    "            if x >= score[col]['成绩'][i]:\n",
    "                return score[col]['分数'][i]\n",
    "        return 0 # 说明跑的太慢了\n",
    "    df_boy[col + '分数'] = df_boy[col].apply(convert)\n",
    "df_boy.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 列索引重排 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['班级', '性别', '男1000米跑','男1000米跑分数',  '男50米跑','男50米跑分数', \n",
    "        '男跳远','男跳远分数', '男体前屈','男体前屈分数', '男引体','男引体分数', \n",
    "        '男肺活量', '男肺活量分数','身高',\n",
    "       '体重', 'BMI']\n",
    "df_boy = df_boy[cols]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 计算BMI体重指数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男50米跑分数</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男跳远分数</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男体前屈分数</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男引体分数</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>男肺活量分数</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>72</td>\n",
       "      <td>8.88</td>\n",
       "      <td>66</td>\n",
       "      <td>195.0</td>\n",
       "      <td>60</td>\n",
       "      <td>12.0</td>\n",
       "      <td>74</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>62</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.599998</td>\n",
       "      <td>25.120001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>70</td>\n",
       "      <td>7.70</td>\n",
       "      <td>78</td>\n",
       "      <td>225.0</td>\n",
       "      <td>74</td>\n",
       "      <td>11.0</td>\n",
       "      <td>74</td>\n",
       "      <td>7.0</td>\n",
       "      <td>60</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>68</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.700001</td>\n",
       "      <td>17.410000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>74</td>\n",
       "      <td>8.45</td>\n",
       "      <td>70</td>\n",
       "      <td>218.0</td>\n",
       "      <td>70</td>\n",
       "      <td>14.0</td>\n",
       "      <td>78</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>80</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.500000</td>\n",
       "      <td>16.280001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>68</td>\n",
       "      <td>8.05</td>\n",
       "      <td>74</td>\n",
       "      <td>206.0</td>\n",
       "      <td>64</td>\n",
       "      <td>13.0</td>\n",
       "      <td>76</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>100</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.699997</td>\n",
       "      <td>23.799999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>85</td>\n",
       "      <td>7.52</td>\n",
       "      <td>78</td>\n",
       "      <td>210.0</td>\n",
       "      <td>66</td>\n",
       "      <td>13.0</td>\n",
       "      <td>76</td>\n",
       "      <td>9.0</td>\n",
       "      <td>68</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>74</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.700001</td>\n",
       "      <td>18.709999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.23</td>\n",
       "      <td>68</td>\n",
       "      <td>8.27</td>\n",
       "      <td>72</td>\n",
       "      <td>208.0</td>\n",
       "      <td>66</td>\n",
       "      <td>10.0</td>\n",
       "      <td>72</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>4647.0</td>\n",
       "      <td>100</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.500000</td>\n",
       "      <td>22.440001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>5.19</td>\n",
       "      <td>40</td>\n",
       "      <td>9.55</td>\n",
       "      <td>50</td>\n",
       "      <td>210.0</td>\n",
       "      <td>66</td>\n",
       "      <td>15.0</td>\n",
       "      <td>80</td>\n",
       "      <td>6.0</td>\n",
       "      <td>50</td>\n",
       "      <td>7042.0</td>\n",
       "      <td>100</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.000000</td>\n",
       "      <td>24.260000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3.25</td>\n",
       "      <td>100</td>\n",
       "      <td>7.50</td>\n",
       "      <td>80</td>\n",
       "      <td>252.0</td>\n",
       "      <td>90</td>\n",
       "      <td>13.0</td>\n",
       "      <td>76</td>\n",
       "      <td>13.0</td>\n",
       "      <td>85</td>\n",
       "      <td>5755.0</td>\n",
       "      <td>100</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.000000</td>\n",
       "      <td>19.840000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.39</td>\n",
       "      <td>62</td>\n",
       "      <td>7.81</td>\n",
       "      <td>76</td>\n",
       "      <td>208.0</td>\n",
       "      <td>66</td>\n",
       "      <td>14.0</td>\n",
       "      <td>78</td>\n",
       "      <td>11.0</td>\n",
       "      <td>76</td>\n",
       "      <td>5688.0</td>\n",
       "      <td>100</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.700001</td>\n",
       "      <td>17.480000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  男1000米跑  男1000米跑分数  男50米跑  男50米跑分数    男跳远  男跳远分数  男体前屈  男体前屈分数  \\\n",
       "0     1  男     4.13         72   8.88       66  195.0     60  12.0      74   \n",
       "1     1  男     4.16         70   7.70       78  225.0     74  11.0      74   \n",
       "2     1  男     4.09         74   8.45       70  218.0     70  14.0      78   \n",
       "3     1  男     4.21         68   8.05       74  206.0     64  13.0      76   \n",
       "4     1  男     3.44         85   7.52       78  210.0     66  13.0      76   \n",
       "..   .. ..      ...        ...    ...      ...    ...    ...   ...     ...   \n",
       "472  17  男     4.23         68   8.27       72  208.0     66  10.0      72   \n",
       "473  17  男     5.19         40   9.55       50  210.0     66  15.0      80   \n",
       "474  17  男     3.25        100   7.50       80  252.0     90  13.0      76   \n",
       "475  17  男     4.39         62   7.81       76  208.0     66  14.0      78   \n",
       "476  17  男     0.00          0   0.00        0    0.0      0   0.0       0   \n",
       "\n",
       "      男引体  男引体分数    男肺活量  男肺活量分数     身高         体重        BMI  \n",
       "0     1.0      0  2785.0      62  170.0  72.599998  25.120001  \n",
       "1     7.0     60  3133.0      68  174.0  52.700001  17.410000  \n",
       "2     1.0      0  3901.0      80  169.0  46.500000  16.280001  \n",
       "3     1.0      0  4946.0     100  183.0  79.699997  23.799999  \n",
       "4     9.0     68  3538.0      74  171.0  54.700001  18.709999  \n",
       "..    ...    ...     ...     ...    ...        ...        ...  \n",
       "472   0.0      0  4647.0     100  176.0  69.500000  22.440001  \n",
       "473   6.0     50  7042.0     100  177.0  76.000000  24.260000  \n",
       "474  13.0     85  5755.0     100  181.0  65.000000  19.840000  \n",
       "475  11.0     76  5688.0     100  172.0  51.700001  17.480000  \n",
       "476   0.0      0     0.0       0    0.0   0.000000   0.000000  \n",
       "\n",
       "[477 rows x 17 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = (df_boy['体重']/(df_boy['身高']/100)**2)\n",
    "df_boy['BMI'] = s.fillna(0).round(2) # 保留两位小数\n",
    "df_boy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 保存数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_boy.to_excel('./体测分数_男生.xls',index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 女生分数转换"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对体测成绩进行转换，跑步类（越小越好）；跳远、体前屈（越大越好）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 速度类分数转换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>女800米跑分数</th>\n",
       "      <th>女50米跑分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>9.32</td>\n",
       "      <td>185.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.299999</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>11.44</td>\n",
       "      <td>148.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.599998</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>13.40</td>\n",
       "      <td>150.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>9.52</td>\n",
       "      <td>172.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.700001</td>\n",
       "      <td>0.0</td>\n",
       "      <td>85</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>9.79</td>\n",
       "      <td>145.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.900002</td>\n",
       "      <td>0.0</td>\n",
       "      <td>80</td>\n",
       "      <td>68</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  女800米跑  女50米跑    女跳远  女体前屈   女仰卧    女肺活量     身高         体重  BMI  \\\n",
       "0   1  女    3.22   9.32  185.0  16.0  48.0  3775.0  163.0  51.299999  0.0   \n",
       "1   1  女    4.59  11.44  148.0   9.0  29.0  3683.0  163.0  66.599998  0.0   \n",
       "2   1  女    3.46  13.40  150.0   7.0  40.0  3331.0  157.0  60.000000  0.0   \n",
       "3   1  女    3.39   9.52  172.0  21.0  46.0  3701.0  160.0  50.700001  0.0   \n",
       "4   1  女    3.43   9.79  145.0   8.0  34.0  3592.0  167.0  63.900002  0.0   \n",
       "\n",
       "   女800米跑分数  女50米跑分数  \n",
       "0       100       72  \n",
       "1        40       10  \n",
       "2        80        0  \n",
       "3        85       70  \n",
       "4        80       68  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = ['女800米跑','女50米跑']\n",
    "\n",
    "for col in cols:\n",
    "    def convert(x):\n",
    "        if x == 0: # 说明没有参加体能测试\n",
    "            return 0\n",
    "        for i in range(score[col].shape[0]):\n",
    "            if x <= score[col]['成绩'][i]:\n",
    "                return score[col]['分数'][i]\n",
    "        return 0 # 说明跑的太慢了\n",
    "    df_girl[col + '分数'] = df_girl[col].transform(convert)\n",
    "df_girl.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>女800米跑分数</th>\n",
       "      <th>女50米跑分数</th>\n",
       "      <th>女跳远分数</th>\n",
       "      <th>女体前屈分数</th>\n",
       "      <th>女仰卧分数</th>\n",
       "      <th>女肺活量分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>9.32</td>\n",
       "      <td>185.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.299999</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100</td>\n",
       "      <td>72</td>\n",
       "      <td>85</td>\n",
       "      <td>76</td>\n",
       "      <td>85</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>11.44</td>\n",
       "      <td>148.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.599998</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40</td>\n",
       "      <td>10</td>\n",
       "      <td>60</td>\n",
       "      <td>66</td>\n",
       "      <td>66</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>13.40</td>\n",
       "      <td>150.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>60</td>\n",
       "      <td>64</td>\n",
       "      <td>76</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>9.52</td>\n",
       "      <td>172.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.700001</td>\n",
       "      <td>0.0</td>\n",
       "      <td>85</td>\n",
       "      <td>70</td>\n",
       "      <td>76</td>\n",
       "      <td>90</td>\n",
       "      <td>85</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>9.79</td>\n",
       "      <td>145.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.900002</td>\n",
       "      <td>0.0</td>\n",
       "      <td>80</td>\n",
       "      <td>68</td>\n",
       "      <td>50</td>\n",
       "      <td>64</td>\n",
       "      <td>70</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  女800米跑  女50米跑    女跳远  女体前屈   女仰卧    女肺活量     身高         体重  BMI  \\\n",
       "0   1  女    3.22   9.32  185.0  16.0  48.0  3775.0  163.0  51.299999  0.0   \n",
       "1   1  女    4.59  11.44  148.0   9.0  29.0  3683.0  163.0  66.599998  0.0   \n",
       "2   1  女    3.46  13.40  150.0   7.0  40.0  3331.0  157.0  60.000000  0.0   \n",
       "3   1  女    3.39   9.52  172.0  21.0  46.0  3701.0  160.0  50.700001  0.0   \n",
       "4   1  女    3.43   9.79  145.0   8.0  34.0  3592.0  167.0  63.900002  0.0   \n",
       "\n",
       "   女800米跑分数  女50米跑分数  女跳远分数  女体前屈分数  女仰卧分数  女肺活量分数  \n",
       "0       100       72     85      76     85     100  \n",
       "1        40       10     60      66     66     100  \n",
       "2        80        0     60      64     76     100  \n",
       "3        85       70     76      90     85     100  \n",
       "4        80       68     50      64     70     100  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = ['女跳远', '女体前屈', '女仰卧', '女肺活量']\n",
    "for col in cols:\n",
    "    def convert(x):\n",
    "        if x == 0: # 说明没有参加体能测试\n",
    "            return 0\n",
    "        for i in range(score[col].shape[0]):\n",
    "            if x >= score[col]['成绩'][i]:\n",
    "                return score[col]['分数'][i]\n",
    "        return 0 # 说明跑的太慢了\n",
    "    df_girl[col + '分数'] = df_girl[col].apply(convert)\n",
    "df_girl.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 索引重排"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['班级', '性别', '女800米跑','女800米跑分数',  '女50米跑','女50米跑分数', \n",
    "        '女跳远','女跳远分数', '女体前屈','女体前屈分数', '女仰卧','女仰卧分数', \n",
    "        '女肺活量', '女肺活量分数','身高','体重', 'BMI']\n",
    "df_girl = df_girl[cols]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 计算BMI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女800米跑分数</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女50米跑分数</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女跳远分数</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女体前屈分数</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女仰卧分数</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>女肺活量分数</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>100</td>\n",
       "      <td>9.32</td>\n",
       "      <td>72</td>\n",
       "      <td>185.0</td>\n",
       "      <td>85</td>\n",
       "      <td>16.0</td>\n",
       "      <td>76</td>\n",
       "      <td>48.0</td>\n",
       "      <td>85</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>100</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.299999</td>\n",
       "      <td>19.309999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>40</td>\n",
       "      <td>11.44</td>\n",
       "      <td>10</td>\n",
       "      <td>148.0</td>\n",
       "      <td>60</td>\n",
       "      <td>9.0</td>\n",
       "      <td>66</td>\n",
       "      <td>29.0</td>\n",
       "      <td>66</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>100</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.599998</td>\n",
       "      <td>25.070000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>80</td>\n",
       "      <td>13.40</td>\n",
       "      <td>0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60</td>\n",
       "      <td>7.0</td>\n",
       "      <td>64</td>\n",
       "      <td>40.0</td>\n",
       "      <td>76</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>100</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.000000</td>\n",
       "      <td>24.340000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>85</td>\n",
       "      <td>9.52</td>\n",
       "      <td>70</td>\n",
       "      <td>172.0</td>\n",
       "      <td>76</td>\n",
       "      <td>21.0</td>\n",
       "      <td>90</td>\n",
       "      <td>46.0</td>\n",
       "      <td>85</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>100</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.700001</td>\n",
       "      <td>19.799999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>80</td>\n",
       "      <td>9.79</td>\n",
       "      <td>68</td>\n",
       "      <td>145.0</td>\n",
       "      <td>50</td>\n",
       "      <td>8.0</td>\n",
       "      <td>64</td>\n",
       "      <td>34.0</td>\n",
       "      <td>70</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>100</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.900002</td>\n",
       "      <td>22.910000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>588</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.51</td>\n",
       "      <td>78</td>\n",
       "      <td>9.60</td>\n",
       "      <td>68</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60</td>\n",
       "      <td>24.0</td>\n",
       "      <td>95</td>\n",
       "      <td>41.0</td>\n",
       "      <td>78</td>\n",
       "      <td>2255.0</td>\n",
       "      <td>70</td>\n",
       "      <td>158.0</td>\n",
       "      <td>49.000000</td>\n",
       "      <td>19.629999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>589</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.00</td>\n",
       "      <td>76</td>\n",
       "      <td>10.18</td>\n",
       "      <td>64</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60</td>\n",
       "      <td>13.0</td>\n",
       "      <td>72</td>\n",
       "      <td>36.0</td>\n",
       "      <td>72</td>\n",
       "      <td>2937.0</td>\n",
       "      <td>85</td>\n",
       "      <td>161.0</td>\n",
       "      <td>55.700001</td>\n",
       "      <td>21.490000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.45</td>\n",
       "      <td>80</td>\n",
       "      <td>10.18</td>\n",
       "      <td>64</td>\n",
       "      <td>152.0</td>\n",
       "      <td>62</td>\n",
       "      <td>15.0</td>\n",
       "      <td>76</td>\n",
       "      <td>35.0</td>\n",
       "      <td>72</td>\n",
       "      <td>2592.0</td>\n",
       "      <td>76</td>\n",
       "      <td>165.0</td>\n",
       "      <td>48.599998</td>\n",
       "      <td>17.850000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>591</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.01</td>\n",
       "      <td>74</td>\n",
       "      <td>9.67</td>\n",
       "      <td>68</td>\n",
       "      <td>165.0</td>\n",
       "      <td>70</td>\n",
       "      <td>10.0</td>\n",
       "      <td>68</td>\n",
       "      <td>41.0</td>\n",
       "      <td>78</td>\n",
       "      <td>1829.0</td>\n",
       "      <td>60</td>\n",
       "      <td>154.0</td>\n",
       "      <td>43.599998</td>\n",
       "      <td>18.379999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>592</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.48</td>\n",
       "      <td>50</td>\n",
       "      <td>9.09</td>\n",
       "      <td>74</td>\n",
       "      <td>180.0</td>\n",
       "      <td>80</td>\n",
       "      <td>10.0</td>\n",
       "      <td>68</td>\n",
       "      <td>46.0</td>\n",
       "      <td>85</td>\n",
       "      <td>2962.0</td>\n",
       "      <td>85</td>\n",
       "      <td>162.0</td>\n",
       "      <td>55.299999</td>\n",
       "      <td>21.070000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>593 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  女800米跑  女800米跑分数  女50米跑  女50米跑分数    女跳远  女跳远分数  女体前屈  女体前屈分数  \\\n",
       "0     1  女    3.22       100   9.32       72  185.0     85  16.0      76   \n",
       "1     1  女    4.59        40  11.44       10  148.0     60   9.0      66   \n",
       "2     1  女    3.46        80  13.40        0  150.0     60   7.0      64   \n",
       "3     1  女    3.39        85   9.52       70  172.0     76  21.0      90   \n",
       "4     1  女    3.43        80   9.79       68  145.0     50   8.0      64   \n",
       "..   .. ..     ...       ...    ...      ...    ...    ...   ...     ...   \n",
       "588  17  女    3.51        78   9.60       68  150.0     60  24.0      95   \n",
       "589  17  女    4.00        76  10.18       64  150.0     60  13.0      72   \n",
       "590  17  女    3.45        80  10.18       64  152.0     62  15.0      76   \n",
       "591  17  女    4.01        74   9.67       68  165.0     70  10.0      68   \n",
       "592  17  女    4.48        50   9.09       74  180.0     80  10.0      68   \n",
       "\n",
       "      女仰卧  女仰卧分数    女肺活量  女肺活量分数     身高         体重        BMI  \n",
       "0    48.0     85  3775.0     100  163.0  51.299999  19.309999  \n",
       "1    29.0     66  3683.0     100  163.0  66.599998  25.070000  \n",
       "2    40.0     76  3331.0     100  157.0  60.000000  24.340000  \n",
       "3    46.0     85  3701.0     100  160.0  50.700001  19.799999  \n",
       "4    34.0     70  3592.0     100  167.0  63.900002  22.910000  \n",
       "..    ...    ...     ...     ...    ...        ...        ...  \n",
       "588  41.0     78  2255.0      70  158.0  49.000000  19.629999  \n",
       "589  36.0     72  2937.0      85  161.0  55.700001  21.490000  \n",
       "590  35.0     72  2592.0      76  165.0  48.599998  17.850000  \n",
       "591  41.0     78  1829.0      60  154.0  43.599998  18.379999  \n",
       "592  46.0     85  2962.0      85  162.0  55.299999  21.070000  \n",
       "\n",
       "[593 rows x 17 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = (df_girl['体重']/(df_girl['身高']/100)**2)\n",
    "df_girl['BMI'] = s.fillna(0).round(2) # 保留两位小数\n",
    "df_girl"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 保存数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_girl.to_excel('./体测分数_女生.xls',index = False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6rc1"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
